﻿Imports System.data.SqlClient
Public Class QuanTriDAO
    Public Function CheckEmail(ByVal tk As QuanTriDTO) As Boolean
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "usp_KTTonTaiEmailChuNha"
            cmd.CommandType = CommandType.StoredProcedure

            Dim para As SqlParameter = cmd.Parameters.Add("@Email", SqlDbType.VarChar)
            para.Value = tk.Email

            Dim a As SqlParameter = New SqlParameter("@flag", SqlDbType.Int)
            a.Direction = ParameterDirection.Output
            cmd.Parameters.Add(a)

            cmd.ExecuteScalar()

            Dim flag As Integer = cmd.Parameters("@flag").Value
            If flag = 1 Then
                Return True
            End If
            Return False
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return True
        End Try
        Return False
    End Function
    Public Sub GetSVTK(ByVal tenDN As String, ByRef cn As QuanTriDTO, ByRef tk As TaiKhoanDTO)
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select tk.MaTaiKhoan,tk.TenTaiKhoan,tk.MatKhau,tk.MaLoai,tk.TrangThai,cn.MaQuanTri,cn.HoTen,cn.DienThoai,cn.CMND,cn.Email from QUANTRI cn, TAIKHOAN tk where tk.MaTaiKhoan=cn.MaTaiKhoan and tk.TenTaiKhoan='" + tenDN + "'"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        da.Fill(data)
        Try
            cn.MaQuanTri = Convert.ToInt32(data.Rows(0)("MaQuanTri").ToString())
            cn.MaTaiKhoan = Convert.ToInt32(data.Rows(0)("MaTaiKhoan").ToString())
            cn.HoTen = data.Rows(0)("HoTen").ToString()
            cn.Email = data.Rows(0)("Email").ToString()
            cn.DienThoai = data.Rows(0)("DienThoai").ToString()
            cn.CMND = data.Rows(0)("CMND").ToString()

            tk.MaLoai = Convert.ToInt32(data.Rows(0)("MaLoai").ToString())
            tk.TenTaiKhoan = data.Rows(0)("TenTaiKhoan").ToString()
            tk.MatKhau = data.Rows(0)("MatKhau").ToString()
            tk.MaTaiKhoan = Convert.ToInt32(data.Rows(0)("MaTaiKhoan").ToString())
            tk.TrangThai = Convert.ToInt32(data.Rows(0)("TrangThai").ToString())
        Catch ex As Exception

        End Try

    End Sub
    Public Function GetItemID(ByVal tenDN As String) As QuanTriDTO
        Dim quantri As New QuanTriDTO
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select cn.MaQuanTri,cn.MaTaiKhoan,cn.HoTen,cn.CMND,cn.Email,cn.DienThoai from QUANTRI cn,TAIKHOAN tk where cn.MaTaiKhoan = tk.MaTaiKhoan and tk.TenTaiKhoan='" + tenDN + "'"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        da.Fill(data)
        Try
            quantri.MaQuanTri = Convert.ToInt32(data.Rows(0)("MaQuanTri").ToString())
            quantri.MaTaiKhoan = Convert.ToInt32(data.Rows(0)("MaTaiKhoan").ToString())
            quantri.HoTen = data.Rows(0)("HoTen").ToString()
            quantri.Email = data.Rows(0)("Email").ToString()
            quantri.CMND = data.Rows(0)("CMND").ToString()
            quantri.DienThoai = data.Rows(0)("DienThoai").ToString()
        Catch ex As Exception

        End Try

        Return quantri
    End Function
    Public Function GetItemMaQuanTri(ByVal maquantri As Integer) As QuanTriDTO
        Dim quantri As New QuanTriDTO
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from QUANTRI where MaQuanTri=" + maquantri.ToString()
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        da.Fill(data)
        Try
            quantri.MaQuanTri = Convert.ToInt32(data.Rows(0)("MaQuanTri").ToString())
            quantri.MaTaiKhoan = Convert.ToInt32(data.Rows(0)("MaTaiKhoan").ToString())
            quantri.HoTen = data.Rows(0)("HoTen").ToString()
            quantri.Email = data.Rows(0)("Email").ToString()
            quantri.DienThoai = data.Rows(0)("DienThoai").ToString()
        Catch ex As Exception

        End Try

        Return quantri
    End Function
    Public Function GetAll() As DataTable
        Dim cnn As New SqlConnection
        Dim dataProvider As New DataProvider
        Dim da As SqlDataAdapter
        Dim strCon As String = "select *from QUANTRI"
        cnn = dataProvider.ConnectionData()
        da = New SqlDataAdapter(strCon, cnn)
        Dim data As New DataTable
        Try
            da.Fill(data)
        Catch ex As Exception

        End Try
        Return data
    End Function
    Public Function Insert(ByVal quantri As QuanTriDTO) As Boolean
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_InsertQUANTRI @MaTaiKhoan,@HoTen,@DienThoai,@CMND,@Email,@MaQuanTri"
            Dim para As SqlParameter = cmd.Parameters.Add("@MaTaiKhoan", SqlDbType.Int)
            para.Value = quantri.MaTaiKhoan
            para = cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar)
            para.Value = quantri.HoTen
            para = cmd.Parameters.Add("@DienThoai", SqlDbType.VarChar)
            para.Value = quantri.DienThoai
            para = cmd.Parameters.Add("@CMND", SqlDbType.VarChar)
            para.Value = quantri.CMND
            para = cmd.Parameters.Add("@Email", SqlDbType.VarChar)
            para.Value = quantri.Email
            para = cmd.Parameters.Add("@MaQuanTri", SqlDbType.Int)
            para.Value = quantri.MaQuanTri
            cmd.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return False
        End Try
        Return False
    End Function
    Public Sub Delete(ByVal quantri As QuanTriDTO)
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_DeleteQUANTRI @MaQuanTri"
            Dim para As SqlParameter = cmd.Parameters.Add("@MaQuanTri", SqlDbType.Int)
            para.Value = quantri.MaQuanTri
            cmd.ExecuteNonQuery()
            MessageBox.Show(" đã được xóa thành công", "Thông báo")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
    Public Sub Update(ByVal quantri As QuanTriDTO)
        Try
            Dim data As New DataProvider
            Dim cnn As New SqlConnection
            cnn = data.ConnectionData()
            Dim cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cnn
            cmd.CommandText = "exec usp_UpdateQUANTRI @MaQuanTri,@HoTen,@DienThoai,@Email,@CMND"
            Dim para As SqlParameter = cmd.Parameters.Add("@MaQuanTri", SqlDbType.Int)
            para.Value = quantri.MaQuanTri
            para = cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar)
            para.Value = quantri.HoTen
            para = cmd.Parameters.Add("@DienThoai", SqlDbType.VarChar)
            para.Value = quantri.DienThoai
            para = cmd.Parameters.Add("@Email", SqlDbType.VarChar)
            para.Value = quantri.Email
            para = cmd.Parameters.Add("@CMND", SqlDbType.VarChar)
            para.Value = quantri.CMND
            cmd.ExecuteNonQuery()
            MessageBox.Show(" đã cập nhật tạo thành công", "Thông báo")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
End Class
